/* 
 *   Classname:  com.zzz.db.CSVToOracle
 * 
 *   Date       Developer   Description
 *   2017年11月28日    周成功              新建
 *
 */
package demo.database.csv;

import java.sql.Connection;
import java.util.List;
import java.util.Set;

import javax.sql.DataSource;

import com.titan.core.utils.data.FormatUtils;
import com.titan.core.utils.db.DBUtils;
import com.titan.core.utils.db.DataSourceUtils;
import com.titan.core.utils.db.JdbcUtils;
import com.titan.core.utils.db.RecordSet;
import com.titan.core.utils.dbinfo.DBInfoGlobals;
import com.titan.core.utils.dbinfo.TableInfoUtil;
import com.titan.core.utils.dbinfo.bean.ColumnBean;
import com.titan.core.utils.dbinfo.bean.TableBean;
import com.titan.core.utils.file.CSVUtils;

/**
 *
 *
 * @author 周成功
 *
 */
public class CSVToOracle {
    private static String rootPath  = null;
    private static String ip  = null;
    private static String dbname  = null;
    
    private static void localEnv() {
        ip  = "192.168.128.212";
        dbname = "orcl";
        rootPath = "D:\\我的项目\\项目-四通\\Workspace\\BackupData\\2017-12-01 13.53.36\\";
    }

    public static void main(String[] args) {
        Connection conn = null;
        try {
            localEnv();
            
            String username = "hrbsgts";
            String password = "hrbsgts";
            String driver = "oracle.jdbc.driver.OracleDriver";
            String url = "jdbc:oracle:thin:@" + ip + ":1521:" + dbname;
            
            DataSource datasource = DataSourceUtils.registerDataSource(null, driver, url, username, password, 5, 5);
            JdbcUtils.setDataSource(datasource);
            conn = datasource.getConnection();
            
            // VEHCICLE, VEHCICLE_STATUS, VEHCICLE_STATION
            //     VEHCICLE_AREA, VEHCICLE_AREA_LOT, VEHCICLE_AREA_LOT2
            //     VEHCICLE_WORK, VEHCICLE_RETURN, VEHCICLE_GOODS,
            //     VEHCICLE_STATUS_2_GATE, VEHCICLE_STATUS_2_AREA, VEHCICLE_STATION_2_GATE
            // PERSON_OPERATOR, PERSON_DRIVER, PERSON_MASTER
            //     PERSON_OPERATOR_2_MODULE, PERSON_OPERATOR_2_GATE, PERSON_DRIVER_GROUP_2_GATE, PERSON_DRIVER_2_RULE, PERSON_DRIVER_2_GATE
            // MODULE, LOG_WORK, CONFIG_PARAMETER, BASE_GATE
            //     ID2NAME_COLOR, ID2NAME_CAT
            
//            run(conn, dbname, "VEHCICLE");
//            run(conn, dbname, "VEHCICLE_STATION");
//            run(conn, dbname, "BASE_GATE");
//            run(conn, dbname, "CONFIG_PARAMETER");
//            run(conn, dbname, "ID2NAME_CAT");
//            run(conn, dbname, "ID2NAME_COLOR");
//            run(conn, dbname, "MODULE");
//            run(conn, dbname, "PERSON_DRIVER");
//            run(conn, dbname, "PERSON_DRIVER_GROUP_2_GATE");
//            run(conn, dbname, "PERSON_OPERATOR");
//            run(conn, dbname, "PERSON_OPERATOR_2_MODULE");
//            run(conn, dbname, "VEHCICLE_AREA");
//            run(conn, dbname, "VEHCICLE_AREA_LOT");
//            run(conn, dbname, "VEHCICLE_STATION_2_GATE");
//            run(conn, dbname, "VEHCICLE_STATUS");
//            run(conn, dbname, "VEHCICLE_STATUS_2_AREA");
//            run(conn, dbname, "VEHCICLE_STATUS_2_GATE");
            
            String sql = "select table_name from user_tab_comments";
            RecordSet rest = DBUtils.executeQueryToRecordSet(conn, sql);
            String[] tables = rest.getColumnString("table_name");
            for(int i=0; i<tables.length; i++) {
                try {
                    run(conn, dbname, tables[i]);
                } catch (java.io.FileNotFoundException e) {
                    e.printStackTrace();
                }
            }
        } catch(Exception ex) {
            ex.printStackTrace();
        } finally {
            DBUtils.close(conn);
        }
    }
    
    
    private static void run(Connection conn, String dbname, String tablename) throws Exception {
        String csvFile = rootPath + "" + tablename +".csv";
        run(conn, csvFile, dbname, tablename);
    }
    private static void run(Connection conn, String csvFile, String dbname, String tablename) throws Exception {
        TableBean table = TableInfoUtil.getTableInfo(conn, DBInfoGlobals.DATABASE_TYPE_ORACLE, dbname, tablename);
//        System.out.println(table);
        List<String[]> lines = CSVUtils.read(csvFile);
        if( lines==null || lines.size()<2 ) {
            return;
        }
        String[] columns = lines.get(0);
        StringBuilder sql = new StringBuilder(128);
        StringBuilder sqlValues = new StringBuilder(128);
        
        for (int i = 0; i < columns.length; i++) {
            ColumnBean bean = table.getColumn(columns[i]);
            if( i==0 ) {
                sql.append("insert into ").append(tablename).append("(");
                sqlValues.append(") values (");
            } else {
                sql.append(",");
                sqlValues.append(",");
            }
            sql.append(bean.getNameEn());
            sqlValues.append("?");
        }
        sql.append(sqlValues).append(")");
//        System.out.println(sql);
        
        JdbcUtils.update("delete from " + tablename);
        for (int i = 1; i < lines.size(); i++) {
            String[] line = lines.get(i);
            Object[] args = new Object[columns.length];
            for (int j = 0; j < line.length; j++) {
                ColumnBean bean = table.getColumn(columns[j]);
                if(line[j]==null) {
                    args[j] = null;
                }
                if("DATE".equalsIgnoreCase(bean.getSqltype())) {
                    args[j] = FormatUtils.toDate(line[j]);
                } else if( "VARCHAR2".equalsIgnoreCase(bean.getSqltype()) || "VARCHAR".equalsIgnoreCase(bean.getSqltype()) ) {
                    args[j] = FormatUtils.toString(line[j]);
                } else if( "NUMBER".equalsIgnoreCase(bean.getSqltype()) ) {
                    if( bean.getDatascale()==0 ) {
                        args[j] = FormatUtils.toLong(line[j]);
                    } else {
                        args[j] = FormatUtils.toDouble(line[j]);
                    }
                } else {
                    args[j] = FormatUtils.toString(line[j]);
                    System.out.println(bean);
                }
            }
            JdbcUtils.update(sql.toString(), args);
        }
    }

}
